間違ったインデックスの順番はパフォーマンス問題の原因になる

前節では、既にあるインデックスの列の順番を変えることで、インデックスから得られるメリットを大きくする方法について説明しました。 しかし、例に挙げたのは2つのSQL文だけでした。本来は、インデックスを変更 すると、そのインデックスが作成されたテーブルに対する全てのクエリが影響を受けます。この節では、データベースがどのようにインデックスを選ぶのかを 説明し、既存のインデックスを変更することによる副作用を見ていきます。

前節で採用したEMPLOYEES_PKインデックスは、 子会社だけで検索する全てのクエリのパフォーマンスを改善します。実際のところこのインデックスは、他にどのような検索条件が追加されたと しても、SUBSIDIARY_IDを検索に使う全てのクエリに使うことができます。つまり、where句の他の部分で 他のインデックスを使っていたクエリに対しても有効になるということです。この場合、複数のアクセスパスがある時には、オプティマイザがどの インデックスが最適かを判断する役目を持ちます。

クエリオプティマイザ(あるいはクエリプランナ)は、SQL文を実行 計画に変換する、データベースのコンポーネントの1つです。この処理は、 コンパイルまたはパースとも呼ばれます。オプティマイザには、2つの種類があります。

コストベースオプティマイザ (CBO)は、複数の実行計画のバリエーションを出し、それぞれの実行に必要な コストを計算します。このコストは、実行される処理の 種類と、アクセスされる予定の行数を元にして割り出します。最終的に、 このコスト値が、「ベストな」実行計画を選ぶための基準になるのです。

ルールベースオプティマイザ (RBO)は、ハードコードされたルールを元に実行計画を決定します。 ルールベースオプティマイザは、柔軟性に欠けることから、今日ではあまり使われません。

インデックスを変更すると、好ましくない副作用が出る場合もあります。 ここでは、社内用の社員検索アプリケーションが合併後から非常に遅くなってしまった例を考えてみましょう。まず最初に、以下のクエリがスローダウンの 原因でないかどうかを調べます。

実行計画は以下のようになります。

実行計画では、インデックスを使っており、その合計コストは30と出ています。まあまあの結果でしょう。ただし、変更したばかりの インデックスを使っている点が少々気になるところです。これは、インデックスの変更がパフォーマンスに影響を及ぼしている可能性を 疑うには十分だと言えるでしょう。変更前のインデックスが EMPLOYEE_IDから始まっていたことを覚えていればなおさらです。つまり、where句に含まれていない列から 始まっていたわけです。従って、以前はこのクエリはインデックスを使えなかったはずです。

詳しい調査のためには、変更前後の実行計画を比べてみるのがよいでしょう。元々の実行計画を見るために、以前のインデックス定義をもう一度 適用してみることもできますが、多くのデータベースでは、あるクエリに対してインデックスを使わないようにする簡単な方法が提供されています。 以下は、Oracleにおけるその機能であるオプティマイザヒントの例です。

インデックスの変更前に使われていたであろう実行計画では、インデックスを使っていませんでした。

TABLE ACCESS FULLでは、全テーブルを 読み込む必要がありますが、この場合はインデックスを使うよりも高速なようです。1行のみに一致するクエリでは、こういったことはあまり ないことです。1行のみを検索するのにインデックスを使うのは、フルテーブルスキャンより高速なはずですが、今回はそうではありませんでした。 インデックスが悪さをしているように思えます。

このような時には、問題のある実行計画を順に追っていきましょう。 まず最初は、EMPLOYEES_PKインデックスでのINDEX RANGE SCANです。このインデックスは、LAST_NAMEを含んでいませんので、INDEX RANGE SCANは SUBSIDIARY_IDのフィルタだけを使っているものと考え られます。Oracleでは、これについて“Predicate Information”(述語情報)の欄に、今回の場合は実行計画のエントリ2番として 書かれています。ここで、各処理にどの条件が適用されたかがわかります。

付録A, 「実行計画」,では、他のデータベースにおける“Predicate Information”(述語情報)の確認方法について取り上げています。

ID 2(例2.1)のINDEX RANGE SCANでは、SUBSIDIARY_ID=30 フィルタだけを適用しています。つまり、SUBSIDIARY_IDが 30の最初のエントリを見つけるために、インデックスツリーをたどります。それから、その子会社に属する全てのエントリを見つけるために、 リーフノードチェーンをたどります。INDEX RANGE SCANの 結果は、SUBSIDIARY_IDの条件を満たすROWIDsの リストになります。子会社の規模によって、このリストは数行かもしれませんし、数百行かもしれません。

次は、TABLE ACCESS BY INDEX ROWIDの処理です。 前段階で得たROWIDを使って、テーブルから行(の全ての列)を 取得します。LAST_NAME列が得られたら、データベースはwhere句の残りの部分を評価します。つまり、データベースは LAST_NAMEのフィルタを適用する前に、SUBSIDIARY_ID=30 に一致する全ての行を取得する必要があることになります。

SQL文の応答時間は、結果のサイズではなく、該当する子会社の従業員数に依存します。子会社に従業員が少数しかいない場合、 INDEX RANGE SCANのパフォーマンスは比較的良くなります。 しかし、大きな子会社の場合はTABLE ACCESS FULLの方が、 テーブルから大きなデータを一度に読みだすので、より速くなる可能性もあります(「フルテーブルスキャン」を参照)。

この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。

インデックスの探索で、従業員一人一人にあたるROWIDsを たくさん返す場合には、その後データベースが1行ずつデータを読み出すことに なるので、クエリは遅くなります。インデックスを遅くする、完璧な組み合わせ ――それが、広い範囲のインデックス探索と、たくさんの行を1行ずつ読み出す処理です。

最適な実行計画を選ぶには、テーブル上のデータの分散状態にも依存 します。そのため、オプティマイザはデータベースの中身についての統計情報も 利用します。今回の例では、子会社間の従業員の分散状態を含むヒストグラムが使われます。これにより、オプティマイザはインデックス探索でどのくらいの 行が返されるかを見積もり、それをコスト計算に使うことができます。

コストベースのオプティマイザは、テーブルや列、インデックスの 統計情報を使います。多くの統計情報は、一意な値の数、最大あるいは最小値 (データの範囲)、NULL値の出現回数、ヒストグラム(データの分散状態)といった行レベルの情報からなります。 テーブルに関する最も重要な統計情報は、テーブル自体のサイズ(行数およびブロック数)です。

一方、インデックスに関する最も重要な統計情報は、ツリーの深さ、 リーフノードの数、一意なキーの数、クラスタ化係数 (第5章5, 「データのクラスタリング: インデックスの強力さの2つ目」を参照)です。

オプティマイザはこれらの値を、where句の条件の 選択性を決めるために使います。

データが削除されるなどして統計情報が使えない時には、オプティマイザは デフォルト値を使います。Oracleのデフォルト統計情報は、中程度の選択性と小さなインデックスの組み合わせです。デフォルトを使う場合、 INDEX RANGE SCANが40行を返すと見積もります。 実行計画では、この見積もりはRows列に表示されます(改めて例2.1を参照)。すぐ分かるように、子会社に1000人の従業員がいた場合には、 これはひどく過小な見積もりになってしまいます。

正しい統計情報を提供できているうちは、オプティマイザはよい仕事をしてくれます。以下の実行計画では、INDEX RANGE SCANでは1000行を返すという、新しい見積もりをしていることが分かります。 その結果、次のテーブルアクセスにより高いコストがかかると算出しています。

680というコスト値は、FULL TABLE SCANを使った時のコスト値(477)よりも大きくなって いるので、オプティマイザは自動的にFULL TABLE SCANを選択します。

この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。

この遅いインデックスの例で、正しいインデックスを作成することが最適な解決策でないと言いたいわけではありません。もちろん、姓で 検索するには、LAST_NAMEに作ったインデックスが役に立ってくれることでしょう。

新しいインデックスを使った場合、オプティマイザはコスト値を3と見積もります。

オプティマイザの見積もりによると、インデックスを使うと1行しかアクセスしないようです。データベースは、テーブルから1行しか 読み込まないのです。これは、間違いなくFULL TABLE SCAN よりも高速です。正しく定義されたインデックスは、元のフルテーブルスキャンよりもやはり高速でした。

例2.1と例2.2の2つの実行計画は、ほとんど同じ結果になりました。2つ目の実行計画の方が パフォーマンスは良いですが、データベースは同じ処理を行い、オプティマイザは同じようなコスト値を算出しました。INDEX RANGE SCANの 効率は、かなり広い範囲に渡って変わり、特にテーブルアクセスが後に続く時に 顕著に変わります。インデックスを使えば、SQL文が最も効率のよい方法で実行されるとは限らないのです。